package br.com.foxline.atlasmobile.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.content.SharedPreferences;
import br.com.foxline.atlasmobile.entities.Annex;
import br.com.foxline.atlasmobile.entities.Endereco;
import br.com.foxline.atlasmobile.entities.Imovel;
import br.com.foxline.atlasmobile.enums.SituacaoImovel;
import br.com.foxline.atlasmobile.util.UtilConnection;

import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.dao.RawRowMapper;
import com.j256.ormlite.stmt.QueryBuilder;

public class ImovelDao extends GenericDao<Imovel> {

	private Context context;

	private SharedPreferences prefs;

	public ImovelDao(Context context) {
		super(context, new Imovel());
		this.context = context;
	}

	@Override
	public Imovel create(Imovel entity) {
		return super.create(entity);
	}

	public List<Imovel> export() {
		List<Imovel> results = new ArrayList<Imovel>();
		try {
			prefs = context.getSharedPreferences(
					UtilConnection.APP_SHARED_PREFS, 0);

			if (prefs.getBoolean(UtilConnection.CHECK, true)) {
				results = getDao().queryBuilder().where().eq("enviado", false)
						.and().eq("situacaoImovel", SituacaoImovel.CONCLUIDO)
						.query();
			} else {
				results = getDao().queryBuilder().where().eq("enviado", false)
						.and().ne("situacaoImovel", SituacaoImovel.CANCELADO)
						.query();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return results;

	}

	public List<Imovel> getAllFromList(List<Long> idsImovel) {

		List<Imovel> results = new ArrayList<Imovel>();
		try {

			results = getDao().queryBuilder().where().eq("enviado", true).and().in("id", idsImovel).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return results;

	}
	
	public Imovel getEnviado(Long id) {

		List<Imovel> results = new ArrayList<Imovel>();
		try {

			results = getDao().queryBuilder().where().eq("enviado", true).and().eq("id", id).query();
			return results.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	@Override
	public List<Imovel> findAll() {
		List<Imovel> list = new ArrayList<Imovel>();
		try {
			list = getDao().queryBuilder().orderBy("id", false).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (list == null) {
			return new ArrayList<Imovel>();
		}
		return list;
	}

	public List<Imovel> findPaginatorAll(Long qtd, Long start) {
		List<Imovel> list = new ArrayList<Imovel>();
		try {
			list = getDao().queryBuilder().orderBy("id", false).limit(qtd)
					.offset(start).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (list == null) {
			return new ArrayList<Imovel>();
		}
		return list;
	}

	/**
	 * Meus imóveis por grupo
	 * 
	 * @return
	 */
	public List<Imovel> myProperties(String group, Long qtd, Long start) {
		List<Imovel> results = new ArrayList<Imovel>();
		try {
			results = getDao().queryBuilder().orderBy("id", false).limit(qtd)
					.offset(start).where().like("groupCreate", group).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * Imóveis pendentes
	 * 
	 * @return
	 */
	public List<Imovel> pending(Long qtd, Long start) {
		List<Imovel> results = new ArrayList<Imovel>();
		try {
			results = getDao().queryBuilder().orderBy("id", false).limit(qtd)
					.offset(start).where()
					.ne("situacaoImovel", SituacaoImovel.CONCLUIDO).and()
					.ne("situacaoImovel", SituacaoImovel.CANCELADO).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * Imóveis concluidos
	 * 
	 * @return
	 */
	public List<Imovel> completed(Long qtd, Long start) {
		List<Imovel> results = new ArrayList<Imovel>();
		try {
			results = getDao().queryBuilder().orderBy("id", false).limit(qtd)
					.offset(start).where()
					.eq("situacaoImovel", SituacaoImovel.CONCLUIDO).query();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * Imóveis concluidos
	 * 
	 * @return
	 */
	public List<Imovel> findInscricao(Integer inscricaoCadastralD,
			Integer inscricaoCadastralS, Integer inscricaoCadastralQuadra,
			Integer inscricaoCadastralLote, Integer inscricaoCadastralUnid,
			String logradouro, Integer numero, String proprietario) {

		List<Imovel> results = new ArrayList<Imovel>();

		try {

			String sql = "Select i.id, i.inscricaoCadastralD, i.inscricaoCadastralS, i.inscricaoCadastralQuadra,"
					+ " i.inscricaoCadastralLote, i.inscricaoCadastralUnid, e.id, "
					+ " e.codLogradouro, e.logradouro, e.numero, e.compl, e.codBairro, e.bairro, e.municipio, "
					+ " e.cep, e.uf, i.situacaoImovel, p.nome"
					+ " from Imovel i, Endereco e, Proprietario p where i.enderecoImovel = e.id ";
			if (proprietario != null && !proprietario.equals("")) {
				sql += " and p.imovel = i.id" + "  and p.nome like '%"
						+ proprietario + "%'";
			}
			if (inscricaoCadastralD != null)
				sql += " and i.inscricaoCadastralD = " + inscricaoCadastralD;
			if (inscricaoCadastralS != null)
				sql += " and inscricaoCadastralS = " + inscricaoCadastralS;
			if (inscricaoCadastralQuadra != null)
				sql += " and i.inscricaoCadastralQuadra = "
						+ inscricaoCadastralQuadra;
			if (inscricaoCadastralLote != null)
				sql += " and i.inscricaoCadastralLote = "
						+ inscricaoCadastralLote;
			if (inscricaoCadastralUnid != null)
				sql += " and i.inscricaoCadastralUnid = "
						+ inscricaoCadastralUnid;
			if (logradouro != null && !logradouro.equals(""))
				sql += " and e.logradouro like '%" + logradouro + "%'";
			if (numero != null)
				sql += " and e.numero = " + numero;
			sql += " GROUP BY i.id  ORDER BY i.id DESC";
			GenericRawResults<Imovel> result = getDao().queryRaw(sql,
					new RawRowMapper<Imovel>() {
						public Imovel mapRow(String[] columnNames,
								String[] resultColumns) {
							new Imovel();

							return new Imovel(Long.parseLong(resultColumns[0]),
									parseInt(resultColumns[1]),
									parseInt(resultColumns[2]),
									parseInt(resultColumns[3]),
									parseInt(resultColumns[4]),
									parseInt(resultColumns[5]), new Endereco(
											parseLong(resultColumns[6]),
											parseInt(resultColumns[7]),
											resultColumns[8],
											parseInt(resultColumns[9]),
											resultColumns[10],
											parseInt(resultColumns[11]),
											resultColumns[12],
											resultColumns[13],
											resultColumns[14],
											resultColumns[15]), SituacaoImovel
											.parse(resultColumns[16]));
						}
					});
			System.err.println(sql);
			return result.getResults();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new ArrayList<Imovel>();
	}

	public Integer parseInt(String str) {
		if (str != null && !str.equals(""))
			return Integer.parseInt(str);
		return null;

	}

	public Long parseLong(String str) {
		if (str != null && !str.equals(""))
			return Long.parseLong(str);
		return null;

	}
}
